const sequelize = require('../config/database');

async function showTables() {
  try {
    // 获取所有表名
    const [tables] = await sequelize.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = DATABASE()
      ORDER BY table_name
    `);

    console.log('数据库中的表结构：\n');

    // 对于每个表，显示其创建语句
    for (const table of tables) {
      const tableName = table.TABLE_NAME;
      
      // 获取表结构
      const [columns] = await sequelize.query(`
        SELECT 
          COLUMN_NAME,
          COLUMN_TYPE,
          IS_NULLABLE,
          COLUMN_DEFAULT,
          EXTRA
        FROM 
          information_schema.columns 
        WHERE 
          table_schema = DATABASE()
          AND table_name = ?
        ORDER BY 
          ordinal_position
      `, {
        replacements: [tableName]
      });

      // 获取索引信息
      const [indexes] = await sequelize.query(`
        SELECT 
          INDEX_NAME,
          NON_UNIQUE,
          GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as COLUMNS
        FROM 
          information_schema.statistics
        WHERE 
          table_schema = DATABASE()
          AND table_name = ?
        GROUP BY 
          INDEX_NAME, NON_UNIQUE
      `, {
        replacements: [tableName]
      });

      // 获取外键信息
      const [foreignKeys] = await sequelize.query(`
        SELECT 
          CONSTRAINT_NAME,
          COLUMN_NAME,
          REFERENCED_TABLE_NAME,
          REFERENCED_COLUMN_NAME
        FROM 
          information_schema.key_column_usage
        WHERE 
          table_schema = DATABASE()
          AND table_name = ?
          AND REFERENCED_TABLE_NAME IS NOT NULL
      `, {
        replacements: [tableName]
      });

      console.log(`表名: ${tableName}`);
      
      console.log('\n列定义:');
      columns.forEach(col => {
        console.log(`${col.COLUMN_NAME} ${col.COLUMN_TYPE} ${col.IS_NULLABLE === 'YES' ? 'NULL' : 'NOT NULL'} ${col.COLUMN_DEFAULT ? `DEFAULT ${col.COLUMN_DEFAULT}` : ''} ${col.EXTRA}`);
      });

      console.log('\n索引:');
      indexes.forEach(idx => {
        console.log(`${idx.INDEX_NAME} (${idx.COLUMNS}) ${idx.NON_UNIQUE === 0 ? 'UNIQUE' : ''}`);
      });

      if (foreignKeys.length > 0) {
        console.log('\n外键约束:');
        foreignKeys.forEach(fk => {
          console.log(`${fk.CONSTRAINT_NAME}: ${fk.COLUMN_NAME} -> ${fk.REFERENCED_TABLE_NAME}(${fk.REFERENCED_COLUMN_NAME})`);
        });
      }

      console.log('\n' + '-'.repeat(80) + '\n');
    }
  } catch (error) {
    console.error('获取表结构时出错：', error);
  } finally {
    await sequelize.close();
  }
}

showTables(); 